#对平行狗蛋商家进行分阶段结算
require 'open-uri'
load 'rds_api.rb'

def get_paraller_merchants
    sql = "select id, name from crmeb.eb_system_store where is_parallel > 0"
    res = queryRds sql
    merchants = {}
    res.each {|r| merchants.store r['id'], r}
    return merchants
end

def settle_to_uid uid, settle_id, amount, remark

    bill_id = 0

    if not_been_settled uid, settle_id

        puts "settle user ##{uid} with #{amount} for #{remark}"

        sql = "select now_money from crmeb.eb_user where uid = #{uid}"
        balance = 0
        res = queryRds sql
        res.each do |r|
            balance = r['now_money']
            break
        end

        current_tupay = balance.to_f.round(2)
        puts "1) current tupay balance: #{current_tupay}"
        puts "2) settle amount total: #{amount}"

        balance += amount

        sqls = []
        sqls += ["update crmeb.eb_user set now_money = #{balance} where uid = #{uid}"]
        msg = "执行清算单##{settle_id}支付#{amount}元余额"
        link_info = "SETTLE-#{settle_id}"
        add_time = Time.now.to_i
        sqls += ["INSERT INTO crmeb.eb_user_bill ( uid, link_id, pm, title, category, type, number, balance, mark, add_time, status, take) VALUES ( #{uid}, '#{link_info}', 1, '#{remark}', 'now_money', 'brokerage', #{amount}, #{balance}, '#{msg}', #{add_time}, 1, 0);"]
        commitTrans sqls
        sql = "select max(id) as bill_id from crmeb.eb_user_bill where add_time = #{add_time}"
        res = queryRds sql
        bill_id = 0
        res.each do |r|
            bill_id = r['bill_id']
            break
        end

    end

    return bill_id

end

def not_been_settled uid, settle_id
    link_info = "SETTLE-#{settle_id}" 
    sqls = "select count(*) as recs from crmeb.eb_user_bill where uid = #{uid} and link_id = '#{link_info}'"
    res = queryRds sqls
    res.each do |r|
        return r['recs'] == 0 ? true : false
    end
    return false
end

def get_example_merchants
    sql = "select id, name from crmeb.eb_system_store where id = 2 and owner_uid > 0"
    res = queryRds sql
    merchants = {}
    res.each {|r| merchants.store r['id'], r}
    return merchants
end

def get_fc_merchants
    sql = "select id, name from crmeb.eb_system_store where is_parallel = 0"
    res = queryRds sql
    merchants = {}
    res.each {|r| merchants.store r['id'], r}
    return merchants
end

def get_orders sql
    orders ={}
    (queryRds sql).each do |r|
        orders.store r['id'], r
    end
    return orders
end

def get_excel_uri ids, mer_id
    url = "https://mall.foodtrust.cn/api/export_settle?status=1&mer_id=#{mer_id}&excel=9&ids=#{ids}"
    return url
end

def read_settle_amount xlsx
    s = SimpleSpreadsheet::Workbook.read xlsx
    s.selected_sheet = s.sheets.first
    return s.cell(s.last_row,24)
end

def uri_to_file uri, fn
    data = open(uri, 'User-Agent' => 'ruby'){|f| f.read} 
    file = File.new fn, 'w+'
    file.binmode 
    file << data 
    file.flush 
    file.close
    return fn
end

def settle_orders orders, mer_id, fn, type, memo, rate
    if orders.size > 0

        order_ids = []
        orders.each { |id, order| order_ids += [id] }

        uri = get_excel_uri order_ids.join(','), mer_id
        uri_to_file uri, fn
        puts "#{type}: #{orders.size} done"

        amount = (read_settle_amount fn).to_f.round(2)
        final_paid = ( amount * ( 100 - rate ) / 100 ).to_f.round(2)
        request_time = Time.now.to_i
        attachment = "https://mall.foodtrust.cn/uploads/settle/#{fn}"

        sql = "insert into crmeb.eb_settle(mer_id, amount, rate, final_paid, request_time, memo, attachment) values(
            #{mer_id}, #{amount}, #{rate}, #{final_paid}, #{request_time}, '#{memo}', '#{@rds.escape attachment}')"
        queryRds sql


        sql = "select max(id) as settle_id from crmeb.eb_settle where attachment = '#{@rds.escape attachment}'"
        res = queryRds sql
        settle_id = 0
        res.each do |r|
            settle_id = r['settle_id']
            break
        end


        if ( settle_id > 0) 

            #在这里看mer_id是否有owner_uid，且owner_uid允许提现，如果有owner_uid,会将结算金额计入其now_money,且更新paid_time,及payment_sn
            owner_uid = 0
            sql = "select uid from crmeb.eb_user where uid in (select owner_uid from crmeb.eb_system_store where id = #{mer_id} ) ";
            res = queryRds sql
            res.each { |r| owner_uid = r['uid']; break }
            if owner_uid > 0
                bill_id = settle_to_uid owner_uid, settle_id, final_paid, '自动支付清算单'
                puts "=======#{bill_id}======="
                if bill_id > 0
                    sql = "update crmeb.eb_settle set paid_time = #{Time.now.to_i}, payment_sn = 'USER_BILL##{bill_id}' where id = #{settle_id}"
                    puts sql
                    queryRds sql
                end
            end

            #在这里更新订单清算记录
            sql = "update crmeb.eb_store_order set settle = 9, third_settle_id = #{settle_id} where id in (#{order_ids.join(',')})" if type == 'channel'
            sql = "update crmeb.eb_store_order set settle = 9, owner_settle_id = #{settle_id} where id in (#{order_ids.join(',')})" if type == 'independant' || type == 'consign'
            queryRds sql
        end

    end
end

#settle_merchant_by_id 5, '后台周期结算', 0.01, '2021-05-01', '2021-05-31'
def settle_merchant_by_id merchant, title, rate, start_day, end_day

    mer_id = merchant['id']
    start_time = (Time.parse start_day + ' 00:00:00').to_i
    end_time = (Time.parse end_day + ' 23:59:59').to_i

    puts "settle ##{mer_id} #{merchant['name']}..."
    title = "#{title} #{start_day} #{end_day} #{merchant['name']}"


    #独立订单
    sql = "select * from crmeb.eb_store_order where paid > 0 and add_time > #{start_time} and add_time < #{end_time} "
    sql +=  " and mer_id = #{mer_id} and source_mid = 0 "
    sql +=  " and settle = 0 and owner_settle_id = 0 "
    orders = get_orders sql
    type = 'independant'
    fn = "FC4-M#{mer_id}-orders-#{type}-#{start_day}-#{end_day}.xlsx"
    settle_orders orders, mer_id, fn, type, title, rate

    #代发货订单
    sql = "select * from crmeb.eb_store_order where paid > 0 and add_time > #{start_time} and add_time < #{end_time} "
    sql +=  " and mer_id != #{mer_id} and source_mid = #{mer_id} "
    sql +=  " and settle = 0 and third_settle_id = 0 "
    orders = get_orders sql
    type = 'channel'
    fn = "FC4-M#{mer_id}-orders-#{type}-#{start_day}-#{end_day}.xlsx"
    settle_orders orders, mer_id, fn, type, title, rate

    #代销售订单
    sql = "select * from crmeb.eb_store_order where paid > 0 and add_time > #{start_time} and add_time < #{end_time} "
    sql +=  " and mer_id = #{mer_id} and source_mid > 0 "
    sql +=  " and settle = 0 and owner_settle_id = 0 "
    orders = get_orders sql
    type = 'consign'
    fn = "FC4-M#{mer_id}-orders-#{type}-#{start_day}-#{end_day}.xlsx"
    settle_orders orders, mer_id, fn, type, title, rate

    puts "done"

end

#settle_merchant_by_id mer_id, '董学锋手工结算', 0.01, '2020-01-01', '2021-04-30'
#exit

def settle_settleable_merchant merchant, title, rate

    mer_id = merchant['id']
    this_time = Time.now.to_i.to_s

    puts "settle ##{mer_id} #{merchant['name']}..."
    title = "#{title} #{Time.now} #{merchant['name']}"

    #独立订单
    sql = "select * from crmeb.eb_store_order where paid > 0 "
    sql +=  " and mer_id = #{mer_id} and source_mid = 0 "
    sql +=  " and settle = 1 and owner_settle_id = 0 "
    orders = get_orders sql
    type = 'independant'
    fn = "FC4-M#{mer_id}-orders-#{type}-#{this_time}.xlsx"
    settle_orders orders, mer_id, fn, type, title, rate

    #代发货订单
    sql = "select * from crmeb.eb_store_order where paid > 0 "
    sql +=  " and mer_id != #{mer_id} and source_mid = #{mer_id} "
    sql +=  " and settle = 1 and third_settle_id = 0 "
    orders = get_orders sql
    type = 'channel'
    fn = "FC4-M#{mer_id}-orders-#{type}-#{this_time}.xlsx"
    settle_orders orders, mer_id, fn, type, title, rate

    #代销售订单
    sql = "select * from crmeb.eb_store_order where paid > 0 "
    sql +=  " and mer_id = #{mer_id} and source_mid > 0 "
    sql +=  " and settle = 1 and owner_settle_id = 0 "
    orders = get_orders sql
    type = 'consign'
    fn = "FC4-M#{mer_id}-orders-#{type}-#{this_time}.xlsx"
    settle_orders orders, mer_id, fn, type, title, rate

    puts "done"

end

def settle_001 #2021-08-24
    merchants = get_paraller_merchants
    merchants.each do |mer_id, merchant|
        if mer_id != 9995
            settle_merchant_by_id merchant, '手工结算', 2, '2020-01-01', '2021-04-30'
            settle_merchant_by_id merchant, '手工结算', 2, '2021-05-01', '2021-06-30'
            settle_merchant_by_id merchant, '手工结算', 1, '2021-07-01', '2021-07-31'
            settle_merchant_by_id merchant, '手工结算', 0.6, '2021-08-01', '2021-08-20'
        end
    end
end

def settle_002 #2021-08-30
    merchants = get_paraller_merchants
    merchants.each do |mer_id, merchant|
        settle_merchant_by_id merchant, '手工结算', 0.6, '2021-08-21', '2021-08-27'
    end
end

def settle_003 #以mer_id#2为例，对拥有owner_uid的用户的商户进行清算时将清算结果增加至余额
    merchants = get_example_merchants
    merchants.each do |mer_id, merchant|
        settle_settleable_merchant merchant, '自动结算', 0.6
    end
end

def auto_settle_parallers
    merchants = get_paraller_merchants
    merchants.each do |mer_id, merchant|
        settle_settleable_merchant merchant, '自动结算', 0.6
    end
end

def settle_merchant_by mer_id, settle_id, final_paid
    #这里还要改一下,final_paid和mer_id都从settle_id中来，包括名字中来 TBO

            #在这里看mer_id是否有owner_uid，且owner_uid允许提现，如果有owner_uid,会将结算金额计入其now_money,且更新paid_time,及payment_sn
            owner_uid = 0
            sql = "select uid, tupay_level from crmeb.eb_user where uid in (select owner_uid from crmeb.eb_system_store where id = #{mer_id} )";
            res = queryRds sql
            res.each { |r| owner_uid = r['uid']; break }
            if owner_uid > 0
                bill_id = settle_to_uid owner_uid, settle_id, final_paid, '自动支付清算单'
                puts "=======#{bill_id}======="
                if bill_id > 0
                    sql = "update crmeb.eb_settle set paid_time = #{Time.now.to_i}, payment_sn = 'USER_BILL##{bill_id}' where id = #{settle_id} and payment_sn is NULL"
                    puts sql
                    queryRds sql
                end
            end

end
#settle_merchant_by 28, 32, 3218.29
#settle_merchant_by 28, 33, 0.00

def get_mer_orders mer_id
    sql = "select * from crmeb.eb_store_order where paid > 0 and ( mer_id = 18 and source_mid = 0 ) or (mer_id != 18 and source_mid = 18)"
    orders = get_orders sql
    puts "orders size: #{orders.size}"
    order_ids = []
    orders.each { |id, order| order_ids += [id] }
    uri = get_excel_uri order_ids.join(','), mer_id
    puts "uri: #{uri}"
    fn = "FC4-M#{mer_id}-orders-all.xlsx"
    uri_to_file uri, fn
end
#get_mer_orders 18

auto_settle_parallers
